const db = require("../config/mysql");

class SupplierModel {
  constructor() {
    this.db = db;

    // supplier 所有字段
    this.supplierFields = [
      "supplier_id",
      "supplier_name",
      "cate_ids_arr",
      "contact_person",
      "phone",
      "address",
      "bank_name",
      "bank_account",
      "status",
      "rating_level",
      "rating_record",
      "created_at",
      "updated_at",
      "notes",
    ]
      .map((f) => `s.${f}`)
      .join(", ");
  }

  // 获取供应商列表（搜索、过滤、分页）
  async getAllSuppliers({
    page = 1,
    pageSize = 10,
    keyword = "",
    contactPerson = "",
    status = "active", // 默认查询非逻辑删除的供应商 (status != -1)
    rating = null,
    cate_ids = null,
  }) {
    const offset = (page - 1) * pageSize;
    const limit = pageSize;

    let conditions = "WHERE 1=1 ";
    const params = [];

    // 状态过滤 (status='active' 默认排除 status=-1)
    if (status === "active") {
      conditions += "AND s.status != -1 ";
    } else if (status !== null && status !== undefined) {
      conditions += "AND s.status = ? ";
      params.push(parseInt(status));
    }

    // 评级过滤
    if (rating) {
      conditions += "AND s.rating_level = ? ";
      params.push(rating);
    }

    // 按名称模糊搜索
    if (keyword) {
      conditions += "AND (s.supplier_name LIKE ?) ";
      params.push(`%${keyword}%`);
    }

    // 按联系人模糊搜索
    if (contactPerson) {
      conditions += "AND (s.contact_person LIKE ?) ";
      params.push(`%${contactPerson}%`);
    }

    // 按分类搜索
    if (cate_ids) {
      // 确保 cate_ids 是一个整数，用于查询
      const categoryId = parseInt(cate_ids);
      if (!isNaN(categoryId)) {
        // 使用 JSON_CONTAINS 检查 cate_ids_arr 数组中是否包含指定的 categoryId
        conditions += "AND JSON_CONTAINS(s.cate_ids_arr, ?) ";
        params.push(JSON.stringify(categoryId));
      } else {
        // 如果传入的 cate_ids 无法解析为有效数字，则忽略或可以抛出错误
        console.warn(`Invalid category ID passed: ${cate_ids}`);
      }
    }

    // 查询总数
    const totalSql = `SELECT COUNT(*) AS total FROM supplier s ${conditions}`;

    const [totalResult] = await this.db.query(totalSql, params);
    const total = totalResult[0].total;

    // 查询数据列表
    let dataSql = `SELECT ${this.supplierFields}, (SELECT COUNT(*) FROM goods g WHERE g.supplier_id = s.supplier_id) AS goods_count
    FROM supplier s ${conditions}
    ORDER BY
    FIELD(s.rating_level, 'A', 'B', 'C', 'D'), 
    s.updated_at DESC
    LIMIT ? OFFSET ?`;

    // 重新构建 params 数组，因为 LIMIT/OFFSET 在 COUNT 之后
    const listParams = params.concat(limit, offset);

    try {
      const [dataResult] = await this.db.query(dataSql, listParams);
      // 反序列化 cate_ids_arr 并重命名为 cate_ids
      const dataList = dataResult.map((item) => {
        let cateIds = [];
        // 尝试解析数据库字段 cate_ids_arr
        if (item.cate_ids_arr) {
          try {
            // 将 JSON 字符串 '[1, 2, 3]' 转换为数组 [1, 2, 3]
            cateIds = JSON.parse(item.cate_ids_arr).map(Number);
          } catch (e) {
            console.error(
              `Error parsing cate_ids_arr for supplier ${item.supplier_id}:`,
              item.cate_ids_arr,
              e
            );
          }
        }
        // 从结果中解构出除 cate_ids_arr 之外的所有字段
        const { cate_ids_arr, ...rest } = item;

        return {
          ...rest,
          cate_ids: cateIds,
        };
      });

      return {
        total,
        data: dataList,
        page: page,
        pageSize: pageSize,
      };
    } catch (error) {
      console.error(error.message);
      throw new Error("无法从数据库中获取供应商列表");
    }
  }

  // 根据ID获取单个供应商详情
  async getSupplierById(supplierId) {
    // 使用明确的字段列表
    const sql = `SELECT ${this.supplierFields} FROM supplier s WHERE s.supplier_id = ?`;

    try {
      const [result] = await this.db.query(sql, [supplierId]);
      const data = result[0] || null;

      if (data) {
        let cateIds = [];
        // 尝试解析数据库字段 cate_ids_arr
        if (data.cate_ids_arr) {
          try {
            cateIds = JSON.parse(data.cate_ids_arr).map(Number);
          } catch (e) {
            console.error(
              `Error parsing cate_ids_arr for supplier ${supplierId}:`,
              data.cate_ids_arr,
              e
            );
          }
        }
        // 从原始数据中删除 cate_ids_arr 字段，并将其作为 cate_ids 插入
        const { cate_ids_arr, ...rest } = data;

        return {
          ...rest,
          cate_ids: cateIds,
        };
      }
      return data;
    } catch (error) {
      console.error(error.message);
      throw new Error("无法获取供应商详情");
    }
  }

  // 新增供应商
  async addSupplier(data) {
    // 序列化 cate_ids 数组为 JSON 字符串
    let cateIdsArr = null;
    // 检查 Controller 传入的 cate_ids 字段
    if (data.cate_ids && Array.isArray(data.cate_ids)) {
      cateIdsArr = JSON.stringify(data.cate_ids);
    }

    const sql = `INSERT INTO supplier (supplier_name, cate_ids_arr, contact_person, phone, address, bank_name, bank_account, status, rating_level, rating_record, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

    const params = [
      data.supplier_name,
      cateIdsArr,
      data.contact_person,
      data.phone || null,
      data.address || null,
      data.bank_name || null,
      data.bank_account || null,
      data.status || 1,
      data.rating_level || "B",
      data.rating_record || null,
      data.notes || null,
    ];

    try {
      const [result] = await this.db.query(sql, params);
      return result.insertId;
    } catch (error) {
      if (error.code === "ER_DUP_ENTRY") {
        throw new Error("供应商名称已存在");
      }
      console.error(error.message);
      throw new Error("新增供应商失败");
    }
  }

  // 更新供应商信息
  async updateSupplier(supplierId, data) {
    const updateFields = [];
    const updateValues = [];

    // 动态构建 SQL 语句
    for (const key in data) {
      if (
        Object.hasOwnProperty.call(data, key) &&
        key !== "supplier_id" &&
        key !== "created_at"
      ) {
        let value = data[key];
        // 检查应用层字段 cate_ids，并将其映射到数据库字段 cate_ids_arr
        if (key === "cate_ids" && Array.isArray(data.cate_ids)) {
          updateFields.push("cate_ids_arr = ?");
          updateValues.push(JSON.stringify(data.cate_ids)); // 序列化
        } else if (key !== "cate_ids") {
          // 处理其他字段
          updateFields.push(`${key} = ?`);
          updateValues.push(value);
        }
      }
    }

    // 没有需要更新的字段
    if (updateFields.length === 0) {
      return true;
    }

    const sql = `UPDATE supplier SET ${updateFields.join(
      ", "
    )} WHERE supplier_id = ?`;
    updateValues.push(supplierId);

    try {
      const [result] = await this.db.query(sql, updateValues);
      return result.affectedRows > 0;
    } catch (error) {
      if (error.code === "ER_DUP_ENTRY") {
        throw new Error("供应商名称已存在");
      }
      console.error(error.message);
      throw new Error("更新供应商信息失败");
    }
  }

  // 检查供应商名称是否已存在
  async isNameExist(name, excludeId = null) {
    let sql = `SELECT COUNT(*) AS count FROM supplier WHERE supplier_name = ?`;
    const params = [name];

    if (excludeId) {
      sql += ` AND supplier_id != ?`;
      params.push(excludeId);
    }

    try {
      const [result] = await this.db.query(sql, params);
      return result[0].count > 0;
    } catch (error) {
      console.error(error.message);
      throw new Error("检查供应商名称唯一性失败");
    }
  }

  // 逻辑删除供应商 (更新状态为 -1-废弃)
  async deleteSupplier(supplierId) {
    const sql = `UPDATE supplier SET status = -1 WHERE supplier_id = ?`;

    try {
      const [result] = await this.db.query(sql, [supplierId]);
      return result.affectedRows > 0;
    } catch (error) {
      console.error(error.message);
      throw new Error("供应商删除失败（状态更新失败）");
    }
  }
}

module.exports = SupplierModel;
